package dao.oracle;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import bean.KeretaApiBean;
import bean.loader.KeretaApiLoader;
import dao.DAOFactory;
import dao.ResourceManager;

public class KeretaApiDao {
	
	private DAOFactory factory;
	
	
	public KeretaApiDao(DAOFactory factory) {
		super();
		this.factory = factory;
	}
	public KeretaApiBean getKeretaApiWhereNoKeretaAndIdJenis(int noKA, String idJenisKA) throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM KERETA_API WHERE NO_KERETA_API = ? AND ID_JENIS_KA = ?");
			ps.setInt(1, noKA);
			ps.setString(2, idJenisKA);
			rs = ps.executeQuery();
			if(rs.next())
				return new KeretaApiLoader().loadSingle(rs);
			return null;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException(e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(ps);
		}
	}
	public List<KeretaApiBean> getListKeretaApi() throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM KERETA_API ORDER BY ID_JENIS_KA ASC");
			rs = ps.executeQuery();
			return new KeretaApiLoader().loadList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException(e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(ps);
		}
	}
	public List<KeretaApiBean> getListKeretaApi(String jenisKA) throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM KERETA_API WHERE ID_JENIS_KA = ? ORDER BY ID_JENIS_KA ASC");
			ps.setString(1, jenisKA);
			rs = ps.executeQuery();
			return new KeretaApiLoader().loadList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException(e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(ps);
		}
	}
	public KeretaApiBean getAvailable(String jenisKA) throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM KERETA_API WHERE ID_JENIS_KA = ? AND AVAILABLE = ? ORDER BY ID_JENIS_KA ASC");
			ps.setString(1, jenisKA);
			ps.setBoolean(2, true);
			rs = ps.executeQuery();
			if(rs.next())
				return new KeretaApiLoader().loadSingle(rs);
			else 
				return null;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException(e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(ps);
		}
	}
	public int update(KeretaApiBean bean) throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("UPDATE KERETA_API SET AVAILABLE = ? WHERE NO_KERETA_API = ? AND ID_JENIS_KA = ?");
			ps.setBoolean(1, bean.isAvailable());
			ps.setInt(2, bean.getNoKeretaApi());
			ps.setString(3, bean.getJenisKeretaApi().getIdJenisKa());
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException(e);
		} finally{
			ResourceManager.close(ps);
		}
	}
}
